<?php
class Search_Model_Yachinsoubaensen extends Search_Model_Yachinsouba{

	/**
	* 駅データ取得
	*/
	public function getEkiData($params)
	{
		$db_params = array();
		$sql = $this->generateEkiSql($params, $db_params);
		return $this->dbGetList($sql, $db_params);
	}

	/**
	 * 駅データ検索用SQL文の取得
	 *
	 * 駅データ検索用SQL文の取得を行う
	 * @access	public
	 * @param	array
	 * @param	array
	 * @return	string 駅データ検索用SQL
	 */
	public function generateEkiSql($params,&$db_params)
	{

		$db_params['StCd'] = $params['StCd'][0];

		$sql = "";
		$sql .= "SELECT DISTINCT ";
		$sql .= "   M03_ENSEN_CD ";
		$sql .= "  ,M03_ENSEN_NAME ";
		$sql .= "  ,M03_EKI_CD ";
		$sql .= "  ,M03_EKI_NAME ";
		$sql .= " FROM M03EKI ";
		$sql .= " WHERE ";
		$sql .= " M03_EKI_CD = :StCd ";

	   return $sql;
	}

	/**
	* 家賃相場（沿線）データ取得
	*/
	public function getYachinsoubaEnsen($params)
	{
		$db_params = array();
		$sql = $this->generateYachinsoubaEnsen($params, $db_params);
		return $this->dbGetList($sql, $db_params);
	}

	/**
	 * 家賃相場（沿線）データ検索用SQL文の取得
	 *
	 * @access	public
	 * @param	array
	 * @param	array
	 * @return	string 家賃相場（沿線）データ検索用SQL
	 */
	public function generateYachinsoubaEnsen($params, &$db_params)
	{
		// 表示日を取得
		$ymd = $this->getYachinsoubaDate();
		if (!empty($ymd))
			$db_params['ymd'] = $ymd['YMD'];
		else
			$db_params['ymd'] = date('Ymd')-1;
		
		$sql  = "";
		$sql .= "SELECT";
		$sql .= "   A02.A02_KEY_CD as KEY_CD ";
		$sql .= " , M03.M03_ENSEN_NAME as PAR_NAME ";
		$sql .= " , M03.M03_EKI_NAME   as KEY_NAME ";
		$sql .= " , A02.MADORI as MADORI ";
		$sql .= " , avg(A02.A02_YACHIN_AVG) as YACHIN_AVG ";
		$sql .= "FROM";
		$sql .= " ( SELECT";
		$sql .= "      A02_KEY_CD ";
		$sql .= "    , case";
		$sql .= "        when A02_MADORI_CD in ('10') THEN '1R'";
		$sql .= "        when A02_MADORI_CD in ('11','12','13') THEN '1K'";
		$sql .= "        when A02_MADORI_CD in ('14','15') THEN '1LDK'";
		$sql .= "        when A02_MADORI_CD in ('20','21','22','23') THEN '2K'";
		$sql .= "        when A02_MADORI_CD in ('24','25') THEN '2LDK'";
		$sql .= "        when A02_MADORI_CD in ('30','31','32','33') THEN '3K'";
		$sql .= "        when A02_MADORI_CD in ('34','35') THEN '3LDK'";
		$sql .= "        when A02_MADORI_CD >= '40' and A02_MADORI_CD < '80' THEN '4K'";
		$sql .= "        else '99'";
		$sql .= "      end MADORI ";
		$sql .= "    , A02_YACHIN_AVG ";
		$sql .= "   FROM A02ESTATE_MKT_PRICE_DAY30 ";
		$sql .= "   WHERE A02_DATA_KUBUN = 21";
		$sql .= "     AND A02_TARGET_YMD = :ymd ";
		$sql .= "     AND SUBSTR(A02_KEY_CD,1,4) = :RailCd ";
		if($params['type'] == 2) {
			$sql .= " AND A02_TATEMONO_TYPE_CD IN ('2')";
		}else if($params['type'] == 3) {
			$sql .= " AND A02_TATEMONO_TYPE_CD IN ('1','3','4','10')";
		}else if($params['type'] == 4) {
			$sql .= " AND A02_TATEMONO_TYPE_CD IN ('5','6','7','8','9')";
		}else {
			$sql .= " AND A02_TATEMONO_TYPE_CD IN ('1','2','3','4','5','6','7','8','9','10')";
		}
		$sql .= " ) A02 ";
		$sql .= ",( SELECT DISTINCT";
		$sql .= "      M03_ENSEN_CD ";
		$sql .= "    , M03_ENSEN_NAME ";
		$sql .= "    , M03_EKI_CD ";
		$sql .= "    , M03_EKI_NAME ";
		$sql .= "   FROM M03EKI ";
		$sql .= "   WHERE M03_ENSEN_CD = :RailCd ";
		$sql .= " ) M03 ";
		$sql .= "WHERE MADORI <> '99' ";
		$sql .= "  AND A02.A02_KEY_CD = M03.M03_EKI_CD ";
		$sql .= "GROUP BY";
		$sql .= "   A02.A02_KEY_CD ";
		$sql .= " , M03.M03_ENSEN_NAME ";
		$sql .= " , M03.M03_EKI_NAME ";
		$sql .= " , A02.MADORI ";
		$sql .= "ORDER BY";
		if ($params['sortKey'] >= 1 and $params['sortKey'] <= 8) {
			$sql .= " decode(MADORI, ";
			switch ($params['sortKey']) {
				case 1:
					$sql .= "'1R'";
					break;
				case 2:
					$sql .= "'1K'";
					break;
				case 3:
					$sql .= "'1LDK'";
					break;
				case 4:
					$sql .= "'2K'";
					break;
				case 5:
					$sql .= "'2LDK'";
					break;
				case 6:
					$sql .= "'3K'";
					break;
				case 7:
					$sql .= "'3LDK'";
					break;
				case 8:
					$sql .= "'4K'";
					break;
			}
			$sql .= ", YACHIN_AVG, ";
			if ($params['sortValue'] == 1) {
				$sql .= " null ";
			} else {
				$sql .= " 0 ";
			}
			$sql .= ") ";
		} else {
			$sql .= " A02.A02_KEY_CD ";
		}
		if ($params['sortValue'] != 1) {
			$sql .= " desc ";
		}
		
		$db_params['RailCd'] = $params['RailCd'][0];
		
		return $sql;
	}

	/**
	* 家賃相場（沿線）データ取得
	*/
	public function getYachinsoubaEnsenSyosaiData($params)
	{
		$db_params = array();
		$sql = $this->generateYachinsoubaEnsenSyosaiSql($params, $db_params);
		return $this->dbGetList($sql, $db_params);
	}

	/**
	 * 家賃相場（沿線）データ検索用SQL文の取得
	 *
	 * 沿線一覧データ検索用SQL文の取得を行う
	 * @access	public
	 * @param	array
	 * @param	array
	 * @return	string 家賃相場（沿線）データ検索用SQL
	 */
	public function generateYachinsoubaEnsenSyosaiSql($params, &$db_params)
	{
		// 表示日を取得
		$ymd = $this->getYachinsoubaDate();
		if (!empty($ymd))
			$db_params['ymd'] = $ymd['YMD'];
		else
			$db_params['ymd'] = date('Ymd')-1;
		
		$sqlWhere  = $this->createWhereStCd("A02_KEY_CD", $params, $db_params);
		$sqlWhere2 = $this->createWhereStCd("M03_EKI_CD", $params, $db_params);
		
		$sql = "";
		$sql .= "SELECT ";
		$sql .= "    A02_KEY_CD, ";
		$sql .= "    M03.M03_ENSEN_NAME as PAR_NAME, ";
		$sql .= "    M03.M03_EKI_NAME   as KEY_NAME, ";
		$sql .= "    ROUND(AVG(A02_YACHIN_AVG)) AS YACHIN_AVG, ";
		$sql .= "    ROUND(AVG(A02_TSUBOYACHIN_AVG)) AS TSUBOYACHIN_AVG, ";
		$sql .= "    ROUND(AVG(A02_SHIKIKIN_AVG)) AS SHIKIKIN_AVG, ";
		$sql .= "    ROUND(AVG(A02_REIKIN_AVG)) AS  REIKIN_AVG, ";
		$sql .= "    MADORI ";
		$sql .= "FROM (";
		$sql .= "  SELECT ";
		$sql .= "      A02_KEY_CD, ";
		$sql .= "      A02_YACHIN_AVG, ";
		$sql .= "      A02_TSUBOYACHIN_AVG, ";
		$sql .= "      A02_SHIKIKIN_AVG, ";
		$sql .= "      A02_REIKIN_AVG, ";
		$sql .= "      CASE ";
		$sql .= "        WHEN A02_MADORI_CD in ('10') THEN '1R'";
		$sql .= "        WHEN A02_MADORI_CD in ('11','12','13') THEN '1K'";
		$sql .= "        WHEN A02_MADORI_CD in ('14','15') THEN '1LDK'";
		$sql .= "        WHEN A02_MADORI_CD in ('20','21','22','23') THEN '2K'";
		$sql .= "        WHEN A02_MADORI_CD in ('24','25') THEN '2LDK'";
		$sql .= "        WHEN A02_MADORI_CD in ('30','31','32','33') THEN '3K'";
		$sql .= "        WHEN A02_MADORI_CD in ('34','35') THEN '3LDK'";
		$sql .= "        WHEN A02_MADORI_CD >= '40' and A02_MADORI_CD < '80' THEN '4K'";
		$sql .= "        ELSE '99'";
		$sql .= "      END AS MADORI ";
		$sql .= "  FROM ";
		$sql .= "      A02ESTATE_MKT_PRICE_DAY30 ";
		$sql .= "  WHERE ";
		$sql .= "      A02_DATA_KUBUN = 21 AND ";		// 沿線
		$sql .= "    A02_TARGET_YMD  = :ymd AND ";
		if($params['type'] == 2){
			$sql .= "  A02_TATEMONO_TYPE_CD IN ('2') AND";
		}else if($params['type'] == 3){
			$sql .= "  A02_TATEMONO_TYPE_CD IN ('1','3','4','10') AND";
		}else if($params['type'] == 4){
			$sql .= "  A02_TATEMONO_TYPE_CD IN ('5','6','7','8','9') AND";
		}else {
			$sql .= "  A02_TATEMONO_TYPE_CD IN ('1','2','3','4','5','6','7','8','9','10') AND";
		}
		$sql .=        $sqlWhere;
		$sql .= "  ) A02";
		$sql .= ",( SELECT DISTINCT";
		$sql .= "      M03_ENSEN_CD ";
		$sql .= "    , M03_ENSEN_NAME ";
		$sql .= "    , M03_EKI_CD ";
		$sql .= "    , M03_EKI_NAME ";
		$sql .= "   FROM M03EKI ";
		$sql .= "   WHERE ";
		$sql .=        $sqlWhere2;
		$sql .= " ) M03 ";
		$sql .= "WHERE MADORI <> '99' ";
		$sql .= "  AND A02.A02_KEY_CD = M03.M03_EKI_CD ";
		$sql .= "GROUP BY ";
		$sql .= "    A02_KEY_CD, ";
		$sql .= "    M03.M03_ENSEN_NAME, ";
		$sql .= "    M03.M03_EKI_NAME, ";
		$sql .= "    MADORI ";
		
		return $sql;
	}

	/**
	* グラフデータ取得
	*/
	public function getYachinsoubaEnsenGraphData($params)
	{
		$db_params = array();
		$sql = $this->generateYachinsoubaEnsenGraphSql($params, $db_params);
		return $this->dbGetList($sql, $db_params);
	}

	/**
	 * 家賃相場（沿線）データグラフ用SQL文の取得
	 *
	 * 市区町村一覧データ検索用SQL文の取得を行う
	 * @access	public
	 * @param	array
	 * @param	array
	 * @return	string 家賃相場（沿線）データグラフ用SQL
	 */
	public function generateYachinsoubaEnsenGraphSql($params,&$db_params)
	{
		$sqlWhere = $this->createWhereStCd("a.A02_KEY_CD", $params, $db_params);

		$sql = "";
		$sql .= "SELECT ";
		$sql .= "    TO_CHAR(TO_DATE(a.A02_TARGET_YM,'YYYYMM'),'YYYY/MM') AS MONTH, ";
		//賃料
		if($params['graphcode'] == 1){
			$sql .= "    AVG(a.A02_YACHIN_AVG)  AS PRICE ";
		//坪賃料
		}else if($params['graphcode'] == 2){
			$sql .= "    AVG(a.A02_TSUBOYACHIN_AVG)  AS PRICE ";
		//敷金
		}else if($params['graphcode'] == 3){
			$sql .= "    AVG(a.A02_SHIKIKIN_AVG)  AS PRICE ";
		//礼金
		}else{
			$sql .= "    AVG(a.A02_REIKIN_AVG)  AS PRICE ";
		}
		$sql .= "FROM ";
		$sql .= "    WEBLOG1_USER.A02ESTATE_MKT_PRICE_MONTH a ";
		$sql .= "WHERE ";
		$sql .= "    TO_DATE(a.A02_TARGET_YM,'YYYYMM') >= ADD_MONTHS(SYSDATE,-13) AND ";
		$sql .= "    TO_DATE(a.A02_TARGET_YM,'YYYYMM') <= ADD_MONTHS(SYSDATE,-1) AND ";
		$sql .=      $sqlWhere." AND ";
		//1R/1K/1DK
		if($params['madori'] == 1){
			$sql .= "    a.A02_MADORI_CD IN ('10','11','12','13') ";
		//1LDK/2K/2DK
		}else if($params['madori'] == 2){
			$sql .= "    a.A02_MADORI_CD IN ('14','15','20','21','22','23') ";
		//2LDK/3K/3DK
		}else if($params['madori'] == 3){
			$sql .= "    a.A02_MADORI_CD IN ('24','25','30','31','32','33') ";
		//3LDK/4K/4DK
		}else{
			$sql .= "    a.A02_MADORI_CD IN ('34','35','40','41','42','43') ";
		}
		$sql .= " AND ";
		$sql .= "    a.A02_DATA_KUBUN = '21' ";
		$sql .= "GROUP BY  ";
		$sql .= "    a.A02_TARGET_YM ";
		$sql .= "ORDER BY  ";
		$sql .= "    a.A02_TARGET_YM ";
		
		return $sql;
	}

	/**
	 * 駅CD where文の作成
	 *
	 * @access	public
	 * @param	array
	 * @return
	 */
	public function createWhereStCd($column, $params, &$db_params)
	{
		$sql = "";
		$spl = "";
		for ($i=0; $i<count($params['StCd']); $i++) {
			if ($i != 0) { $spl = " OR "; }
			$db_params['stCd_' . $i] = $params['StCd'][$i];
			$sql .= $spl."( ".$column." = :stCd_".$i." )";
		}
		return " (".$sql.") ";
	}

	/**
	 * 路線図CDから駅CD取得
	 *
	 * @access	public
	 * @param	array
	 * @return
	 */
	public function redirectFromRosenzu(&$params)
	{
		// 駅データ検索
		$db_params = array();
		$sql = $this->generateStCdFromRosenzuSql($params, $db_params);
		$result = $this->dbGetList($sql, $db_params);

		if (empty($result)) {
			// エラー
			echo "駅コードが不正です。";
			exit;
		} else {
			// パラメータセット
			$params['SrhKbn']    = Apm_Config_Search::SRHKBN_YACHINSOUBA;
			$params['SrhType']   = Apm_Config_Search::SRHTYPE_ENSENEKI;
			$params['Detail']    = Apm_Config_Search::SRHTYPE_ENSENEKI;
			$params['PrefCd']    = array();
			$params['RailCd']    = array();
			$params['StCd']      = array();
			foreach ($result as $key => $val) {
				$params['PrefCd'][] = $val['TODOUFUKEN_CD'];
				$params['RailCd'][] = $val['ENSEN_CD'];
				$params['StCd'][]   = $val['EKI_CD'];
			}
		}
	}

	/**
	 * 路線図CDから駅CD取得SQL
	 *
	 * @access	public
	 * @param	array
	 * @param	array
	 * @return	string 駅CD取得用SQL
	 */
	public function generateStCdFromRosenzuSql($params, &$db_params)
	{
		$db_params['r_eki_cd'] = $params['r_eki_cd'];
		// SQL
		$sql  = "";
		$sql .= " SELECT";
		$sql .= "      M03_TODOUFUKEN_CD as TODOUFUKEN_CD";
		$sql .= "     ,M03_ENSEN_CD as ENSEN_CD";
		$sql .= "     ,M03_EKI_CD as EKI_CD";
		$sql .= " FROM";
		$sql .= "      M03EKI M03";
		$sql .= " WHERE";
		$sql .= "      M03.M03_ROSENZU_EKI_CD = :r_eki_cd ";
		return $sql;
	}

}

?>
